Skip Headers

Oracle® Database Administrator's Guide 10g Release 2 (10.2) Part Number B14231-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 28 Administering the Scheduler

This chapter describes managing a Scheduler environment, including step-by-step instructions for configuring, administering, and monitoring. This chapter contains the following sections:

- Configuring the Scheduler

- Monitoring and Managing the Scheduler

- Import/Export and the Scheduler

- Examples of Using the Scheduler

  Note: This chapter discusses the use of the Oracle-supplied `DBMS_SCHEDULER` package to administer scheduling capabilities. You can also use Oracle Enterprise Manager (EM) as an easy-to-use graphical interface for many of the same capabilities. See the PL/SQL Packages and Types Reference for `DBMS_SCHEDULER` syntax and the Oracle Enterprise Manager documentation set for more information regarding EM.

## Configuring the Scheduler

The following tasks are necessary when configuring the Scheduler:

- Task 1: Setting Scheduler Privileges

- Task 2: Configuring the Scheduler Environment

Task 1: Setting Scheduler Privileges

You should have the `SCHEDULER_ADMIN` role to administer the Scheduler. Typically, database administrators will already have this role with the `ADMIN` option as part of the `DBA` (or equivalent) role. You can grant this role to another administrator by issuing the following statement:

    GRANT SCHEDULER_ADMIN TO username;

Because the `SCHEDULER_ADMIN` role is a powerful role allowing a grantee to execute code as any user, you should consider granting individual Scheduler system privileges instead. Object and system privileges are granted using regular SQL grant syntax. An example is if the database administrator issues the following statement:

    GRANT CREATE JOB TO scott;

After this statement is executed, `scott` can create jobs, schedules, or programs in his schema. Another example is if the database administrator issues the following statement:

    GRANT MANAGE SCHEDULER TO adam;

After this statement is executed, `adam` can create, alter, or drop windows, job classes, or window groups. He will also be able to set and retrieve Scheduler attributes and purge Scheduler logs.

See "How to Manage Scheduler Privileges" for more information regarding privileges.

Setting Chain Privileges

Scheduler chains use underlying Oracle Streams Rules Engine objects along with their associated privileges. To create a chain in his own schema, a user must have the `CREATE` `JOB` privilege in addition to the Rules Engine privileges required to create rules, rule sets, and evaluation contexts in his own schema. These can be granted by issuing the following statement:

    BEGIN
    DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, 'username'),
    DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
       DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 'username'),
    DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
       DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 'username')
    END;
    /

To create a chain in a different schema, a user must have the `CREATE` `ANY` `JOB` privilege in addition to the privileges required to create rules, rule sets, and evaluation contexts in schemas other than his own. These can be granted by issuing the following statement:

    BEGIN
    DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_ANY_RULE, 'username'),
    DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
       DBMS_RULE_ADM.CREATE_ANY_RULE_SET, 'username'),
    DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
       DBMS_RULE_ADM.CREATE_ANY_EVALUATION_CONTEXT, 'username')
    END;
    /

Altering or dropping chains in schemas other than the user's schema will require corresponding system Rules Engine privileges for rules, rule sets, and evaluation contexts. See the usage notes for `DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE` for more information on Streams Rules Engine privileges.

See "How to Manage Scheduler Privileges" for more information regarding privileges.

Task 2: Configuring the Scheduler Environment

This section discusses the following tasks:

- Task 2A: Creating Job Classes

- Task 2B: Creating Windows

- Task 2C: Creating Resource Plans

- Task 2D: Creating Window Groups

- Task 2E: Setting Scheduler Attributes

Task 2A: Creating Job Classes

To create job classes, use the `CREATE_JOB_CLASS` procedure. The following statement illustrates an example of creating a job class:

    BEGIN
    DBMS_SCHEDULER.CREATE_JOB_CLASS (
       job_class_name              => 'my_jobclass1',
       resource_consumer_group     => 'my_res_group1',
       comments                    => 'This is my first job class.');
    END;
    /

This statement creates a job class called `my_jobclass1` with attributes such as a resource consumer group of `my_res_group1`. To verify the job class contents, issue the following statement:

    SELECT * FROM DBA_SCHEDULER_JOB_CLASSES;

    JOB_CLASS_NAME        RESOURCE_CONSU   SERVICE   LOGGING_LEV  LOG_HISTORY    COMMENTS
    -----------------     --------------   -------   -----------  -----------    --------
    DEFAULT_JOB_CLASS                                       RUNS                 The default
    AUTO_TASKS_JOB_CLASS  AUTO_TASK_CON                     RUNS                 System maintenance
    FINANCE_JOBS          FINANCE_GROUP                     RUNS
    MY_JOBCLASS1          MY_RES_GROUP1                     RUNS                 My first job class
    MY_CLASS1                              my_service1      RUNS                 My second job class

    5 rows selected.

Note that job classes are created in the `SYS` schema.

See Also: PL/SQL Packages and Types Reference for `CREATE_JOB_CLASS` syntax, "Creating Job Classes" for further information on job classes, and "Examples of Creating Job Classes" for more examples of creating job classes

Task 2B: Creating Windows

To create windows, use the `CREATE_WINDOW` procedure. The following statement illustrates an example of creating a window:

    BEGIN
    DBMS_SCHEDULER.CREATE_WINDOW (
       window_name     =>  'my_window1',
       resource_plan   =>  'my_resourceplan1',
       start_date      =>  '15-APR-03 01.00.00 AM Europe/Lisbon',
       repeat_interval =>  'FREQ=DAILY',
       end_date        =>  '15-SEP-04 01.00.00 AM Europe/Lisbon',
       duration        =>  interval '50' minute,
       window_priority =>  'HIGH',
       comments        =>  'This is my first window.');
    END;
    /

To verify that the window was created properly, query the view `DBA_SCHEDULER_WINDOWS`. As an example, issue the following statement:

    SELECT WINDOW_NAME, RESOURCE_PLAN, DURATION, REPEAT_INTERVAL
    FROM DBA_SCHEDULER_WINDOWS;

    WINDOW_NAME    RESOURCE_PLAN     DURATION         REPEAT_INTERVAL
    -----------    -------------     -------------    ---------------
    MY_WINDOW1     MY_RESOURCEPLAN1  +000 00:50:00    FREQ=DAILY

See Also: PL/SQL Packages and Types Reference for `CREATE_WINDOW` syntax, "Creating Windows" for further information on windows, and "Examples of Creating Windows" for more examples of creating job classes

Task 2C: Creating Resource Plans

To create resource plans, use the `CREATE_SIMPLE_PLAN` procedure. This procedure enables you to create consumer groups and allocate resources to them by executing a single statement.

The following statement illustrates an example of using this procedure to create a resource plan called `my_simple_plan1`:

    BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN (
       simple_plan       => 'my_simple_plan1',
       consumer_group1   => 'my_group1',
       group1_cpu        => 80,
       consumer_group2   => 'my_group2',
       group2_cpu        => 20);
    END;
    /

This statement creates a resource plan called `my_simple_plan1`. To verify the resource plan contents, query the view `DBA_RSRC_PLANS`. An example is the following statement:

    SELECT PLAN, STATUS FROM DBA_RSRC_PLANS;

    PLAN                           STATUS
    ------------------------------ --------------------------
    SYSTEM_PLAN                    ACTIVE
    INTERNAL_QUIESCE               ACTIVE
    INTERNAL_PLAN                  ACTIVE
    MY_SIMPLE_PLAN1                ACTIVE

See Also: "Allocating Resources Among Jobs" for further information on resource plans

Task 2D: Creating Window Groups

To create window groups, use the `CREATE_WINDOW_GROUP` and `ADD_WINDOW_GROUP_MEMBER` procedures. The following statements illustrate an example of using these procedures:

    BEGIN
    DBMS_SCHEDULER.CREATE_WINDOW_GROUP (
       group_name        =>  'my_window_group1',
       comments          =>  'This is my first window group.');

    DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
       group_name        =>  'my_window_group1',
       window_list       =>  'my_window1, my_window2');

    DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
       group_name        =>  'my_window_group1',
       window_list       =>  'my_window3');
    END;
    /

These statements assume that you have already created `my_window2` and `my_window3`. You can do this with the `CREATE_WINDOW` procedure.

These statements create a window group called `my_window_group1` and then add `my_window1`, `my_window2`, and `my_window3` to it. To verify the window group contents, issue the following statements:

    SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS;

    WINDOW_GROUP_NAME   ENABLED  NUMBER_OF_WINDOWS   COMMENTS
    -----------------   -------  -----------------   --------------------
    MY_WINDOW_GROUP1    TRUE                     3   This is my first window group.

    SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;

    WINDOW_GROUP_NAME              WINDOW_NAME
    ------------------------------ ---------------
    MY_WINDOW_GROUP1               MY_WINDOW1
    MY_WINDOW_GROUP1               MY_WINDOW2
    MY_WINDOW_GROUP1               MY_WINDOW3

See Also: PL/SQL Packages and Types Reference for `CREATE_WINDOW_GROUP` syntax, "Using Window Groups" for further information on window groups, and "Example of Creating Window Groups" for more detailed examples of creating window groups

Task 2E: Setting Scheduler Attributes

There are several Scheduler attributes that control the behavior of the Scheduler. They are `default_timezone`, `log_history`, `max_job_slave_processes`, and `event_expiry_time`. The values of these attributes can be set by using the `SET_SCHEDULER_ATTRIBUTE` procedure. Setting these attributes requires the `MANAGE` `SCHEDULER` privilege. Attributes that can be set are:

- `default_timezone`

  Repeating jobs and windows that use the calendaring syntax need to know which time zone to use for their repeat intervals. They normally retrieve the time zone from `start_date`, but if no `start_date` is provided (which is not uncommon), they retrieve the time zone from the `default_timezone` Scheduler attribute.

  Scheduler derives the value of `default_timezone` from the operating system environment. If Scheduler can find no compatible value from the operating system, it sets `default_timezone` to `NULL`.

  It is crucial that you verify that `default_timezone` is set properly, and if not, that you set it. To verify it, run this query:

      SQL> select dbms_scheduler.stime from dual;

      STIME
      ---------------------------------------------------------------------------
      14-OCT-04 02.56.03.206273000 PM US/PACIFIC

  To ensure that daylight savings adjustments are followed, it is strongly recommended that you set `default_timezone` to a region name instead of an absolute time zone offset. For example, if your database resides in Miami, Florida, USA, issue the following statement:

      DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','US/Eastern');

  To see a list of valid region names, run this query:

      SELECT DISTINCT TZNAME FROM V$TIMEZONE_NAMES;

  If you do not properly set `default_timezone`, the default time zone for repeating jobs and windows will be the absolute offset retrieved from `SYSTIMESTAMP` (the time zone of the operating system environment of the database), which means that repeating jobs and windows that do not have their `start_date` set will not follow daylight savings adjustments.

- `log_history`

  This enables you to control the amount of logging the Scheduler performs. To prevent the job log and the window log from growing indiscriminately, the Scheduler has an attribute that specifies how much history (in days) to keep. Once a day, the Scheduler automatically purges all log entries from both the job log as well as the window log that are older than the specified history. The default is 30 days.

  You can change the default by using the `SET_SCHEDULER_ATTRIBUTE` procedure. For example, to change it to 90 days, issue the following statement:

      DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');

  The range of valid values is 1 through 999.

- `max_job_slave_processes`

  This enables you to set a maximum number of slave processes for a particular system configuration and load. Even though the Scheduler automatically determines what the optimum number of slave processes is for a given system configuration and load, you still might want to set a fixed limit on the Scheduler. If this is the case, you can set this attribute. The default value is `NULL`, and the valid range is 1-999.

  Although the number set by `max_job_slave_processes` is a real maximum, it does not mean the Scheduler will start the specified number of slaves. For example, even though this attribute is set to 10, the Scheduler might still determine that is should not start more than 3 slave processes. However, if it wants to start 15, but it is set to 10, it will not start more than 10.

- `event_expiry_time`

  This enables you to set the time in seconds before an event generated by the Scheduler expires (in other words, is automatically purged from the queue).

See PL/SQL Packages and Types Reference for detailed information about the `SET_SCHEDULER_ATTRIBUTE` procedure.

## Monitoring and Managing the Scheduler

The following sections discuss how to monitor and manage the Scheduler:

- How to View Scheduler Information

- How to View the Currently Active Window and Resource Plan

- How to View Scheduler Privileges

- How to Find Information About Currently Running Jobs

- How the Job Coordinator Works

- How to Monitor and Manage Window and Job Logs

- How to Manage Scheduler Privileges

- How to Drop a Job

- How to Drop a Running Job

- Why Does a Job Fail to Run?

- Job Recovery After a Failure

- How to Change Job Priorities

- How to Monitor Running Chains

- Why Does a Program Become Disabled?

- Why Does a Window Fail to Take Effect?

- How the Scheduler Guarantees Availability

- How to Handle Scheduler Security

- How to Manage the Scheduler in a RAC Environment

### How to View Scheduler Information

You can check Scheduler information by using many views. An example is the following, which shows information for completed instances of `my_job1`:

    SELECT JOB_NAME, STATUS, ERROR#
    FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'MY_JOB1';

    JOB_NAME     STATUS           ERROR#
    --------     --------------   ------
    MY_JOB1      FAILURE           20000

Table 28-1 contains views associated with the Scheduler. The `*_SCHEDULER_JOBS`, `*_SCHEDULER_SCHEDULES`, `*_SCHEDULER_PROGRAMS`, `*_SCHEDULER_RUNNING_JOBS`, `*_SCHEDULER_JOB_LOG`, `*_SCHEDULER_JOB_RUN_DETAILS` views are particularly useful for managing jobs. See Oracle Database Reference for details regarding Scheduler views.

Table 28-1 Scheduler Views

View | Description
--- | ---
    *_SCHEDULER_SCHEDULES
| These views show all schedules.
    *_SCHEDULER_PROGRAMS
| These views show all programs.
    *_SCHEDULER_PROGRAM_ARGS
| These views show all arguments defined for all programs as well as the default values if they exist.
    *_SCHEDULER_JOBS
| These views show all jobs, enabled as well as disabled.
    *_SCHEDULER_RUNNING_CHAINS
| These views show all chains that are running.
    *_SCHEDULER_CHAIN_STEPS
| These views show all steps for all chains.
    *_SCHEDULER_CHAINS
| These views show all chains.
    *_SCHEDULER_CHAIN_RULES
| These views show all rules for all chains.
    *_SCHEDULER_GLOBAL_ATTRIBUTE
| These views show the current values of Scheduler attributes.
    *_SCHEDULER_JOB_ARGS
| These views show all set argument values for all jobs.
    *_SCHEDULER_JOB_CLASSES
| These views show all job classes.
    *_SCHEDULER_WINDOWS
| These views show all windows.
    *_SCHEDULER_JOB_RUN_DETAILS
| These views show all completed (failed or successful) job runs.
    *_SCHEDULER_WINDOW_GROUPS
| These views show all window groups.
    *_SCHEDULER_WINGROUP_MEMBERS
| These views show the members of all window groups, one row for each group member.
    *_SCHEDULER_RUNNING_JOBS
| These views show state information on all jobs that are currently being run.
    *_SCHEDULER_JOB_LOG
| These views show all state changes made to jobs.
    *_SCHEDULER_WINDOW_LOG
| These views show all state changes made to windows.
    *_SCHEDULER_WINDOW_DETAILS
| These views show all completed window runs.

### How to View the Currently Active Window and Resource Plan

You can view the currently active window and the plan associated with it by issuing the following statement:

    SELECT WINDOW_NAME, RESOURCE_PLAN FROM DBA_SCHEDULER_WINDOWS
    WHERE ACTIVE='TRUE';

    WINDOW_NAME                    RESOURCE_PLAN
    ------------------------------ --------------------------
    MY_WINDOW10                    MY_RESOURCEPLAN1

If there is no window active, you can view the active resource plan by issuing the following statement:

    SELECT * FROM V$RSRC_PLAN;

### How to View Scheduler Privileges

You must have the `MANAGE` `SCHEDULER` privilege to administer the Scheduler. Typically, database administrators have this privilege with the `ADMIN` option as part of the `DBA` (or equivalent) role. You can check your current system privileges by issuing the following statement:

    SELECT * FROM SESSION_PRIVS;

If you do not have sufficient privileges, see "Task 1: Setting Scheduler Privileges", "How to Manage Scheduler Privileges", and Oracle Database Security Guide.

### How to Find Information About Currently Running Jobs

You can check a job's state by issuing the following statement:

    SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS
    WHERE JOB_NAME = 'MY_EMP_JOB1';

    JOB_NAME                       STATE
    ------------------------------ ---------
    MY_EMP_JOB1                    DISABLED

In this case, you could enable the job using the `ENABLE` procedure. Table 28-2 shows the valid values for job state.

Table 28-2 Job States

Job State | Description
--- | ---
`disabled` | The job is disabled.
`scheduled` | The job is scheduled to be executed.
`running` | The job is currently running.
`completed` | The job has completed, and is not scheduled to run again.
`stopped` | The job was scheduled to run once and was stopped while it was running.
`broken` | The job is broken.
`failed` | The job was scheduled to run once and failed.
`retry` `scheduled` | The job has failed at least once and a retry has been scheduled to be executed.
`succeeded` | The job was scheduled to run once and completed successfully.
`chain_stalled` | The job is of type chain and has no steps running, no steps scheduled to run, and no event steps waiting on an event, and the chain `evaluation_interval` is set to `NULL`. No progress will be made in the chain unless there is manual intervention.

You can check the progress of currently running jobs by issuing the following statement:

    SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;

Note that, for the column `CPU_USED` to show valid data, the initialization parameter `RESOURCE_LIMIT` must be set to `true`.

You can find out information about a job that is part of a running chain by issuing the following statement:

    SELECT * FROM ALL_SCHEDULER_RUNNING_CHAINS WHERE JOB_NAME='MY_JOB1';

You can check whether the job coordinator is running by searching for a process of the form `cjqNNN`.

See Also: Oracle Database Reference for details regarding the `*_SCHEDULER_RUNNING_JOBS` and `DBA_SCHEDULER_JOBS` views

### How the Job Coordinator Works

The job coordinator background process is automatically started and stopped on an as-needed basis. By default, the coordinator will not be up and running, but the database does monitor whether there are any jobs to be executed, or windows to be opened in the near future. If so it will start the coordinator.

As long as there are jobs or windows running, the coordinator continues to be up. Once there has been a certain period of Scheduler inactivity and there are no jobs or windows scheduled in the near future, the coordinator will automatically be stopped.

#### Job Coordinator and Real Application Clusters

Each RAC instance has its own job coordinator. The database monitoring checks that determine whether or not to start the job coordinator do take the service affinity of jobs into account. For example, if there is only one job scheduled in the near future and the job class to which this job belongs has service affinity for only two out of the four RAC instances, only the job coordinators for those two instances will be started.

#### Using DBMS_SCHEDULER and DBMS_JOB at the Same Time

Even though Oracle recommends you switch from `DBMS_JOB` to `DBMS_SCHEDULER`, `DBMS_JOB` is still supported for backward compatibility. Both Scheduler packages share the same job coordinator, but `DBMS_JOB` does not have the auto start and stop functionality. Instead, the job coordinator is controlled by the `JOB_QUEUE_PROCESSES` initialization parameter. When `JOB_QUEUE_PROCESSES` is set to 0, the coordinator is turned off and when it has a non-zero value it is turned on.The `JOB_QUEUE_PROCESSES` initialization parameter is only used for `DBMS_JOB`. When this parameter is set to a non-zero value, auto start and stop no longer apply because the coordinator will always be up and running. In this case, the coordinator will take care of execution of both `DBMS_SCHEDULER` and `DBMS_JOB` jobs.

If the initialization parameter is set to 0, or if it is not set at all, no `DBMS_JOB` jobs will be run, however, the auto start and stop feature will be used for all `DBMS_SCHEDULER` jobs and windows. If there is a `DBMS_SCHEDULER` job to be executed, the coordinator will be started and the job will be executed. However, `DBMS_JOB` jobs still will not be run.

#### Scheduler Attribute max_job_slave_processes

The initialization parameter `JOB_QUEUE_PROCESSES` only applies to `DBMS_JOB`. When `DBMS_SCHEDULER` is used, the coordinator will automatically determine how many job slaves to start based on CPU load and the number of outstanding jobs. In special scenarios a dba can still limit the maximum number of slaves to be started by the coordinator by setting the `MAX_JOB_SLAVE_PROCESSES` with the `DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE` procedure.

### How to Monitor and Manag e Window and Job Logs

Logs have a new entry for each event that occurs so that you can track historical information. This is different from a queue, where you only want to track the latest status for an item. There are logs for jobs, job runs, and windows.

Job activity is logged in the `*_SCHEDULER_JOB_LOG` views. Altering a job is logged with an operation of `UPDATE`. Dropping a job is logged in these views with an operation of `DROP`.

See Also: Oracle Database Reference for details on the `*_SCHEDULER_JOB_LOG` views and other Scheduler log views.

#### Job Logs

To see the contents of the job log, query the `DBA_SCHEDULER_JOB_LOG` view. An example is the following statement, which shows what happened for past job runs:

    SELECT JOB_NAME, OPERATION, OWNER FROM DBA_SCHEDULER_JOB_LOG;

    JOB_NAME        OPERATION     OWNER
    --------        ---------     -----
    MY_JOB13        CREATE        SYS
    MY_JOB14        CREATE        OE
    MY_NEW_JOB3     ENABLE        SYS
    MY_EMP_JOB1     UPDATE        SYS
    MY_JOB1         CREATE        SCOTT
    MY_EMP_JOB1     UPDATE        SYS
    MY_EMP_JOB      CREATE        SYS
    MY_JOB14        RUN           OE
    MY_JOB14        RETRY_RUN     OE
    MY_JOB14        RETRY_RUN     OE
    MY_JOB14        RETRY_RUN     OE
    MY_JOB14        RETRY_RUN     OE
    MY_JOB14        BROKEN        OE
    MY_JOB14        DROP          OE

When `logging_level` for a job is set to `LOGGING_FULL`, the `additional_info` column of the job log contains the before and after values of the modified attribute on update operations, and contains the values of all attributes on drop operations. This enables you to trace backwards from the current job state to the state of the job on previous job runs.

#### Job Run Details

To further analyze each job run—why it failed, what the actual start time was, how long the job ran, and so on—query the `DBA_SCHEDULER_JOB_RUN_DETAILS` view. As an example, the following statement illustrates the status for `my_job14`:

    select log_id, job_name, status,
    to_char(log_date, 'DD-MON-YYYY HH24:MI') log_date
    from dba_scheduler_job_run_details
    where job_name = 'MY_JOB14';

        LOG_ID JOB_NAME               STATUS       LOG_DATE
    ---------- ---------------------- ------------ -----------------
            69 MY_JOB14               SUCCEEDED    02-JUN-2005 03:14
           124 MY_JOB14               SUCCEEDED    03-JUN-2005 03:15
           133 MY_JOB14               FAILURE      04-JUN-2005 03:00
           146 MY_JOB14               FAILURE      05-JUN-2005 03:01

For every row in `SCHEDULER_JOB_LOG` that is of operation `RUN`, `RETRY_RUN`, or `RECOVERY_RUN`, there will be a corresponding row in the `*_JOB_RUN_DETAILS` view with the same `LOG_ID`. `LOG_DATE` contains the timestamp of the entry, so sorting by `LOG_DATE` should give you a chronological picture of the life of a job.

#### Controlling Job Logging

You can control the amount of logging that the Scheduler performs on jobs at either a class or job level. Normally, you will want to control jobs at a class level as this offers a full audit trail. To do this, use the `logging_level` attribute in the `CREATE_JOB_CLASS` procedure.

For each new class, the creator of the class must specify what the logging level is for all jobs in that class. The three possible options are:

- `DBMS_SCHEDULER.LOGGING_OFF`

  No logging will be performed for any jobs in this class.

- `DBMS_SCHEDULER.LOGGING_RUNS`

  The Scheduler will write detailed information to the job log for all runs of each job in this class.

- `DBMS_SCHEDULER.LOGGING_FULL`

  In addition to recording every run of a job, the Scheduler will record all operations performed on all jobs in this class. In other words, every time a job is created, enabled, disabled, altered, and so on will be recorded in the log.

By default, only job runs are recorded. For job classes that have very short and highly frequent jobs, the overhead of recording every single run might be too much and you might choose to turn the logging off. You might, however, prefer to have a complete audit trail of everything that happened to the jobs in a specific class, in which case you need to turn on full logging for that class.

The second way of controlling the logging level is on an individual job basis. You should keep in mind, however, that the log in many cases is used as an audit trail, thus if you want a certain level of logging, the individual job creator must not be able to turn logging off. The class-specific level is, therefore, the minimum level at which job information will be logged. A job creator can only turn on more logging for an individual job, not less.

This functionality is provided for debugging purposes. For example, if the class-specific level is set to record job runs and the job-specific logging is turned off, the Scheduler will still log the runs. If, on the other hand, the job creator turns on full logging and the class-specific level is set to record runs only, all operations on this individual job will be logged. This way, an end user can test his job by turning on full logging.

To set the logging level of an individual job, you must use the `SET_ATTRIBUTE` procedure on that job. For example, to turn on full logging for a job called `mytestjob`, issue the following statement:

    DBMS_SCHEDULER.SET_ATTRIBUTE (
       'mytestjob', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);

See Also: PL/SQL Packages and Types Reference for detailed information about the `CREATE_JOB_CLASS` and `SET_ATTRIBUTE` procedures and "Task 2E: Setting Scheduler Attributes"

#### Window Logs

A window log has an entry for each time you do the following:

- Create a window

- Drop a window

- Open a window

- Close a window

- Overlap windows

- Disable a window

- Enable a window

There are no logging levels for window logging, but every window operation will automatically be logged by the Scheduler.

To see the contents of the window log, query the `DBA_SCHEDULER_WINDOW_LOG` view. The following statement shows sample output from this view:

    SELECT LOG_ID, TO_CHAR(LOG_DATE, 'MM/DD/YYYY'), WINDOW_NAME, OPERATION
      FROM DBA_SCHEDULER_WINDOW_LOG;

        LOG_ID TO_CHAR(LO WINDOW_NAME       OPERATION
    ---------- ---------- ----------------- ------------------------------
             1 10/01/2004 WEEKNIGHT_WINDOW  CREATE
             2 10/01/2004 WEEKNIGHT_WINDOW  UPDATE
             3 10/01/2004 WEEKNIGHT_WINDOW  UPDATE
             4 10/01/2004 WEEKEND_WINDOW    CREATE
             5 10/01/2004 WEEKEND_WINDOW    UPDATE
             6 10/01/2004 WEEKEND_WINDOW    UPDATE
            22 10/06/2004 WEEKNIGHT_WINDOW  OPEN
            25 10/06/2004 WEEKNIGHT_WINDOW  CLOSE
            26 10/06/2004 WEEKNIGHT_WINDOW  OPEN
            29 10/06/2004 WEEKNIGHT_WINDOW  CLOSE

The `DBA_SCHEDULER_WINDOWS_DETAILS` view provides information about every window that was active and is now closed (completed). The following statement shows sample output from that view:

    SELECT LOG_ID, WINDOW_NAME, ACTUAL_START_DATE, ACTUAL_DURATION
      FROM DBA_SCHEDULER_WINDOW_DETAILS;

        LOG_ID WINDOW_NAME      ACTUAL_START_DATE                    ACTUAL_DURATI
    ---------- ---------------- ------------------------------------ -------------
            25 WEEKNIGHT_WINDOW 06-OCT-04 03.12.48.832438 PM PST8PDT +000 01:02:32
            29 WEEKNIGHT_WINDOW 06-OCT-04 06.19.37.025704 PM PST8PDT +000 03:02:00

Notice that log IDs correspond in both of these views, and that in this case the rows in the `DBA_SCHEDULER_WINDOWS_DETAILS` view correspond to the `CLOSE` operations in the `DBA_SCHEDULER_WINDOW_LOG` view.

#### Purging Logs

To prevent job and window logs from growing indiscriminately, use the `SET_SCHEDULER_ATTRIBUTE` procedure to specify how much history (in days) to keep. Once per day, the Scheduler automatically purges all log entries that are older than the specified history period from both the job log and the window log. The default history period is 30 days. For example, to change the history period to 90 days, issue the following statement:

    DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');

Some job classes are more important than others. Because of this, you can override this global history setting by using a class-specific setting. For example, suppose that there are three job classes (`class1`, `class2`, and `class3`), and that you want to keep 10 days of history for the window log, `class1`, and `class3`, but 30 days for `class2`. To achieve this, issue the following statements:

    DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','10');
    DBMS_SCHEDULER.SET_ATTRIBUTE('class2','log_history','30');

You can also set the class-specific history when creating the job class.

Note that log entries pertaining to steps of a chain run are not purged until the entries for the main chain job are purged.

Purging Logs Manually

The `PURGE_LOG` procedure enables you to manually purge logs. As an example, the following statement purges all entries from both the job and window logs:

    DBMS_SCHEDULER.PURGE_LOG();

Another example is the following, which purges all entries from the jog log that are older than three days. The window log is not affected by this statement.

    DBMS_SCHEDULER.PURGE_LOG(log_history => 3, which_log => 'JOB_LOG');

The following statement purges all window log entries older than 10 days and all job log entries older than 10 days that relate to `job1` and to the jobs in `class2`:

    DBMS_SCHEDULER.PURGE_LOG(log_history => 10, job_name => 'job1, sys.class2');

### How to Manage Sched uler Privileges

You should have the `SCHEDULER_ADMIN` role to administer the Scheduler. Typically, database administrators will already have this role with the `ADMIN` option as part of the `DBA` (or equivalent) role. You can grant this role to another administrator by issuing the following statement:

    GRANT SCHEDULER_ADMIN TO username;

Because the `SCHEDULER_ADMIN` role is a powerful role allowing a grantee to execute code as any user, you should consider granting individual Scheduler system privileges instead. Both system and object privileges are granted using regular SQL grant syntax. An example is for the database administrator to issue the following statement:

    GRANT CREATE JOB TO scott;

After this statement is executed, `scott` can create jobs, schedules, or programs in his schema. Another example is to grant an object privilege, as in the following statement:

    GRANT ALTER myjob1 TO scott;

After this statement is executed, `scott` can execute, alter, or copy `myjob1`. See Oracle Database SQL Reference for system and object privilege details and Oracle Database Security Guide for general information.

An alternative to the `SCHEDULER_ADMIN` role for administering the Scheduler is to use the `MANAGE` `SCHEDULER` privilege, which is recommended for managing resources. As an example of granting this privilege to `adam`, issue the following statement:

    GRANT MANAGE SCHEDULER TO adam;

After this statement is executed, `adam` can create, alter, or drop windows, job classes, or window groups. He will also be able to set and retrieve Scheduler attributes and purge Scheduler logs.

If users will create chains in their own schemas, they need to have the `CREATE` `JOB` privilege in addition to the Rules Engine privileges required to create rules, rule sets, and evaluation contexts in their own schemas. If users will create chains in different schemas, they must have the `CREATE` `ANY` `JOB` privilege in addition to the privileges required to create rules, rule sets, and evaluation contexts in schemas other than their own. See "Setting Chain Privileges" for the statements necessary to enable users to create chains and "Chain Tasks and Their Procedures" for more information regarding chain privileges.

#### Types of Privileges and Their Descriptions

The following privileges are important when using the Scheduler.

Table 28-3 Scheduler Privileges

Privilege Name | Operations Authorized
--- | ---
System Privileges: |
`CREATE` `JOB` | This privilege enables you to create jobs, chains, schedules, and programs in your own schema. You will always be able to alter and drop jobs, schedules and programs in your own schema, even if you do not have the `CREATE` `JOB` privilege. In this case, the job must have been created in your schema by another user with the `CREATE` `ANY` `JOB` privilege.
`CREATE` `ANY` `JOB` | This privilege enables you to create, alter, and drop jobs, chains, schedules, and programs in any schema except `SYS`. This privilege is very powerful and should be used with care because it allows the grantee to execute code as any other user.
`CREATE` `EXTERNAL` `JOB` | This privilege is required to create jobs that run outside of the database. Owners of jobs of type '`EXECUTABLE`' or jobs that point to programs of type '`EXECUTABLE`' require this privilege. To run a job of type '`EXECUTABLE`', you must have this privilege and the `CREATE` `JOB` privilege.
`EXECUTE` `ANY` `PROGRAM` | This privilege enables your jobs to use programs or chains from any schema.
`EXECUTE` `ANY` `CLASS` | This privilege enables your jobs to run under any job class.
`MANAGE` `SCHEDULER` | This is the most important privilege for administering the Scheduler. It enables you to create, alter, and drop job classes, windows, and window groups. It also enables you to set and retrieve Scheduler attributes and purge Scheduler logs.
Object Privileges: |
`EXECUTE` | This privilege can only be granted for programs, chains, and job classes. It enables you to create a job that runs with the program, chain, or job class. It also enables you to view object attributes.
`ALTER` | This privilege enables you to alter or drop the object it is granted on. Altering includes such operations as enabling, disabling, defining or dropping program arguments, setting or resetting job argument values and running a job. For programs, jobs, and chains, this privilege enables you to view object attributes. This privilege can only be granted on jobs, chains, programs and schedules. For other types of Scheduler objects, you can grant the `MANAGE` `SCHEDULER` system privilege. This privilege can be granted for: jobs (`DROP_JOB`, `RUN_JOB`, `ALTER_RUNNING_CHAIN`, `SET_JOB_ARGUMENT_VALUE`, `RESET_JOB_ARGUMENT_VALUE`, `SET_JOB_ANYDATA_VALUE`) and (`STOP_JOB` without the `force` option) chains (`DROP_CHAIN`, `ALTER_CHAIN`, `DEFINE_CHAIN_RULE`, `DEFINE_CHAIN_STEP`, `DEFINE_CHAIN_EVENT_STEP`, `DROP_CHAIN_RULE`, and `DROP_CHAIN_STEP`) programs (`DROP_PROGRAM`, `DEFINE_PROGRAM_ARGUMENT`, `DEFINE_ANYDATA_ARGUMENT`, `DEFINE_METADATA_ARGUMENT`, `DROP_PROGRAM_ARGUMENT`, `SET_ATTRIBUTE_NULL`) schedules (`DROP_SCHEDULE`)
`ALL` | This privilege authorizes operations allowed by all other object privileges possible for a given object. It can be granted on jobs, programs, chains, schedules and job classes.

The `SCHEDULER_ADMIN` role is created with all of the system privileges shown in Table 28-3 (with the `ADMIN` option). The `SCHEDULER_ADMIN` role is granted to `DBA` (with the `ADMIN` option).

The following object privileges are granted to `PUBLIC`: `SELECT` `ALL_SCHEDULER_*` views, `SELECT` `USER_SCHEDULER_*` views, `SELECT` `SYS.SCHEDULER$_JOBSUFFIX_S` (for generating a job name), and `EXECUTE` `SYS.DEFAULT_JOB_CLASS`.

### How to Dro p a Job

You can remove a job from the database by issuing a `DROP_JOB` statement, as in the following:

    BEGIN
    DBMS_SCHEDULER.DROP_JOB (
       job_name    =>  'my_job1');
    END;
    /

See Also: PL/SQL Packages and Types Reference for `DROP_JOB` procedure syntax

### How to Dr op a Running Job

You can delete a running job by issuing the `DROP_JOB` procedure with the `force` option. For example, the following statement forces the deletion of `my_job1`:

    BEGIN
    DBMS_SCHEDULER.DROP_JOB (
       job_name   =>  'my_job1',
       force      =>  TRUE);
    END;
    /

Note that this statement will fail if `my_job1` is running and you do not use the `force` option.

If the `force` option is specified, it will try to stop the job by using an interrupt mechanism. (which would be equivalent to calling `STOP_JOB` without `force` first). Alternatively, you can call `STOP_JOB` to first stop the job and then call `DROP_JOB` to drop it. If you have the `MANAGE` `SCHEDULER` privilege, you can call `STOP_JOB` with `force`, if the regular `STOP_JOB` call failed to stop the job, and then call `DROP_JOB`.

### Why Does a Job Fail to Run?

A job may fail to run for several reasons. Before troubleshooting a job that you suspect did not run, check that the job is not running by issuing the following statement:

    SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;

Typical output will resemble the following:

    JOB_NAME                       STATE
    ------------------------------ ---------
    MY_EMP_JOB                     DISABLED
    MY_EMP_JOB1                    FAILED
    MY_NEW_JOB1                    DISABLED
    MY_NEW_JOB2                    BROKEN
    MY_NEW_JOB3                    COMPLETED

There are four types of jobs that are not running:

- Failed Jobs

- Broken Jobs

- Disabled Jobs

- Completed Jobs

See Also: "Job Recovery After a Failure"

#### Failed Jobs

If a job has the status of `FAILED` in the job table, it was scheduled to run once but the execution has failed. If the job was specified as restartable, all retries have failed.

If a job fails in the middle of execution, only the last transaction of that job is rolled back. If your job executes multiple transactions, you need to be careful about setting `restartable` to `TRUE`. You can query failed jobs by querying the `*_SCHEDULER_JOB_RUN_DETAILS` views.

#### Broken Jobs

A broken job is one that has exceeded a certain number of failures. This number is set in `max_failures`, and can be altered. In the case of a broken job, the entire job is broken, and it will not be run until it has been fixed. For debugging and testing, you can use the `RUN_JOB` procedure.

You can query broken jobs by querying the `*_SCHEDULER_JOBS` and `*_SCHEDULER_JOB_LOG` views.

#### Disabled Jobs

A job can become disabled for the following reasons:

- The job was manually disabled

- The job class it belongs to was dropped

- The program, chain, or schedule that it points to was dropped

- A window or window group is its schedule and the window or window group is dropped

#### Completed Jobs

A job will be completed if `end_date` or `max_runs` is reached. (If a job recently completed successfully but is scheduled to run again, the job state is `SCHEDULED`.)

### Job Rec overy After a Failure

The Scheduler attempts to recover jobs that are interrupted when:

- The database abnormally shuts down

- A job slave process is killed or otherwise fails

- For an external job, the external job process that starts the executable or script is killed or otherwise fails. (The external job process is `extjob` on Unix. On Windows, it is the external job service.)

- For an external job, the process that runs the end-user executable or script is killed or otherwise fails.

Job recovery proceeds as follows:

- The Scheduler adds an entry to the job log for the instance of the job that was running when the failure occurred. In the log entry, the `OPERATION` is '`RUN`', the `STATUS` is '`STOPPED`', and `ADDITIONAL_INFO` contains one of the following:

  - `REASON="Job slave process was terminated"`

  - `REASON="ORA-01014: ORACLE shutdown in progress"`

- If `restartable` is set to `TRUE` for the job, the job is restarted.

- If `restartable` is set to `FALSE` for the job:

  - If the job is a run-once job and `auto_drop` is set to `TRUE`, the job run is done and the job is dropped.

  - If the job is a run-once job and `auto_drop` is set to `FALSE`, the job is disabled and the job `state` is set to '`STOPPED`'.

  - If the job is a repeating job, the Scheduler schedules the next job run and the job `state` is set to '`SCHEDULED`'.

When a job is restarted as a result of this recovery process, the new run is entered into the job log with the operation '`RECOVERY_RUN`'.

### How to Chang e Job Priorities

You can change job priorities by using the `SET_ATTRIBUTE` procedure. Job priorities must be in the range 1-5 with 1 being the highest priority. For example, the following statement changes the job priority for `my_job1` to a setting of 1:

    BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE (
       name           =>   'my_emp_job1',
       attribute      =>   'job_priority',
       value          =>   1);
    END;
    /

You can verify that the attribute was changed by issuing the following statement:

    SELECT JOB_NAME, JOB_PRIORITY FROM DBA_SCHEDULER_JOBS;

    JOB_NAME                       JOB_PRIORITY
    ------------------------------ ------------
    MY_EMP_JOB                                3
    MY_EMP_JOB1                               1
    MY_NEW_JOB1                               3
    MY_NEW_JOB2                               3
    MY_NEW_JOB3                               3

See Also: PL/SQL Packages and Types Reference for detailed information about the `SET_ATTRIBUTE` procedure

### How to M onitor Running Chains

You can check the state of a running chain by querying the `*_SCHEDULER_RUNNING_CHAINS` views. The results contain a row describing the current state of every step in every running instance of a chain. For example, the following statement displays the state of all steps in the running job `MY_CHAIN_JOB`. It also shows the state of all steps of any nested chain jobs that are running or have completed.

    SELECT * FROM USER_SCHEDULER_RUNNING_CHAINS WHERE JOB_NAME = 'MY_CHAIN_JOB';

See "Using Chains" for more information regarding chains.

### Why Does a Program Become Disabled?

A program can become disabled if a program argument is dropped or `number_of_arguments` is changed so that all arguments are no longer defined.

See "Using Programs" for more information regarding programs.

### Why Does a Window Fail to Take Effect?

A window can fail to take effect for the following reasons:

- A window becomes disabled when it is at the end of its schedule

- A window that points to a schedule that no longer exists is disabled

See "Using Windows" for more information regarding windows.

### How the Scheduler Guarantees Availability

You do not need to perform any special operations for the Scheduler in the event of a system or slave process failure.

### How to Handle Sched uler Security

You should grant the `CREATE` `JOB` system privilege to regular users who need to be able to use the Scheduler to schedule and run jobs. You should grant `MANAGE` `SCHEDULER` to any database administrator who needs to be able to manage system resources. Granting any other Scheduler system privilege or role should not be done without great caution. In particular, the `CREATE` `ANY` `JOB` system privilege and the `SCHEDULER_ADMIN` role, which includes it, are very powerful because they allow execution of code as any user. They should only be granted to very powerful roles or users.

A particularly important issue from a security point of view is handling external jobs. Only users that need to run jobs outside of the database should be allowed to do so. You must grant the `CREATE EXTERNAL JOB` system privilege to those users. See "Running External Jobs" for further information. Security for the Scheduler has no other special requirements. See Oracle Database Security Guide for details regarding security.

Note: When upgrading from Oracle Database 10g Release 1 to 10g Release 2, `CREATE EXTERNAL JOB` is automatically granted to all users and roles that have the `CREATE JOB` privilege. Oracle recommends that you revoke this privilege from users that don't need it.

### How to Manage the Scheduler in a RAC Environment

There are no special requirements for using the Scheduler in a RAC environment.

## Import/Export and the Scheduler

You must use the Data Pump utilities (`impdp` and `expdp`) to export Scheduler objects. You cannot use the earlier import/export utilities with the Scheduler. Also, Scheduler objects cannot be exported while the database is in read-only mode.

An export generates the DDL that was used to create the Scheduler objects. All attributes are exported. When an import is done, all the database objects are recreated in the new database. All schedules are stored with their time zones, which are maintained in the new database. For example, schedule "Monday at 1 PM PST in a database in San Francisco" would be the same if it was exported and imported to a database in Germany.

See Also: Oracle Database Utilities for details regarding import and export

## Examples of Using the Scheduler

This section discusses the following topics:

- Examples of Creating Jobs

- Examples of Creating Job Classes

- Examples of Creating Programs

- Examples of Creating Windows

- Examples of Setting Attributes

- Examples of Creating Chains

- Examples of Creating Jobs and Schedules Based on Events

### Examples of Creating Jobs

This section contains several examples of creating jobs. To create a job, you use the `CREATE_JOB` procedure.

Example 28-1 Creating a Job

The following statement creates a job called `my_job1` in the `oe` schema:

    BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
       job_name             => 'oe.my_job1',
       job_type             => 'PLSQL_BLOCK',
       job_action           => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''oe'',
                                ''sales''); END;',
       start_date           => '15-JUL-03 1.00.00AM US/Pacific',
       repeat_interval      => 'FREQ=DAILY',
       end_date             => '15-SEP-03 1.00.00AM US/Pacific',
       enabled              =>  TRUE,
       comments             => 'Gather table statistics');
    END;
    /

This job gathers table statistics on the `sales` table. It will run for the first time on July 15th and then once a day until September 15. To verify that the job was created, issue the following statement:

    SELECT JOB_NAME FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_JOB1';

    JOB_NAME
    ------------------------------
    MY_JOB1

See Also: PL/SQL Packages and Types Reference for detailed information about the `CREATE_JOB` procedure and "Creating Jobs" for further information

### Examples of Creating Job Classes

This section contains several examples of creating job classes. To create a job class, you use the `CREATE_JOB_CLASS` procedure.

Example 28-2 Creating a Job Class

The following statement creates a job class:

    BEGIN
    DBMS_SCHEDULER.CREATE_JOB_CLASS (
       job_class_name              =>  'my_class1',
       service                     =>  'my_service1',
       comments                    =>  'This is my first job class');
    END;
    /

This creates `my_class1` in `SYS`. It uses a service called `my_service1`. To verify that the job class was created, issue the following statement:

    SELECT JOB_CLASS_NAME FROM DBA_SCHEDULER_JOB_CLASSES
    WHERE JOB_CLASS_NAME = 'MY_CLASS1';

    JOB_CLASS_NAME
    ------------------------------
    MY_CLASS1

Example 28-3 Creating a Job Class

The following statement creates a job class:

    BEGIN
    DBMS_SCHEDULER.CREATE_JOB_CLASS (
       job_class_name             =>  'finance_jobs',
       resource_consumer_group    =>  'finance_group',
       comments                   =>  'My financial class');
    END;
    /

This creates `finance_jobs` in `SYS`. It uses a resource consumer group called `finance_group`.

See Also: PL/SQL Packages and Types Reference for detailed information about the `CREATE_JOB_CLASS` procedure and "Creating Job Classes" for further information

### Examples of Creating Programs

This section contains several examples of creating programs. To create a program, you use the `CREATE_PROGRAM` procedure.

Example 28-4 Creating a Program

The following statement creates a program in the `oe` schema:

    BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM (
       program_name          => 'oe.my_program1',
       program_type          => 'PLSQL_BLOCK',
       program_action        => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''oe'',
                                 ''sales''); END;',
       number_of_arguments   => 0,
       enabled               => TRUE,
       comments              => 'My comments here');
    END;
    /

This creates `my_program1`, which uses PL/SQL to gather table statistics on the `sales` table. To verify that the program was created, issue the following statement:

    SELECT PROGRAM_NAME FROM DBA_SCHEDULER_PROGRAMS
    WHERE PROGRAM_NAME = 'MY_PROGRAM1';

    PROGRAM_NAME
    -------------------------
    MY_PROGRAM1

Example 28-5 Creating a Program

The following statement creates a program in the `oe` schema:

    BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM (
       program_name           => 'oe.my_saved_program1',
       program_action         => '/usr/local/bin/date',
       program_type           => 'EXECUTABLE',
       comments               => 'My comments here');
    END;
    /

This creates `my_saved_program1`, which uses an executable.

See Also: PL/SQL Packages and Types Reference for detailed information about the `CREATE_PROGRAM` procedure and "Creating Programs" for further information

### Examples of Creating Windows

This section contains several examples of creating windows. To create a window, you use the `CREATE_WINDOW` procedure.

Example 28-6 Creating a Window

The following statement creates a window called `my_window1` in `SYS`:

    BEGIN
    DBMS_SCHEDULER.CREATE_WINDOW (
       window_name          =>  'my_window1',
       resource_plan        =>  'my_res_plan1',
       start_date           =>  '15-JUL-03 1.00.00AM US/Pacific',
       repeat_interval      =>  'FREQ=DAILY',
       end_date             =>  '15-SEP-03 1.00.00AM US/Pacific',
       duration             =>  interval '80' MINUTE,
       comments             =>  'This is my first window');
    END;
    /

This window will open once a day at 1AM for 80 minutes every day from May 15th to October 15th. To verify that the window was created, issue the following statement:

    SELECT WINDOW_NAME FROM DBA_SCHEDULER_WINDOWS WHERE WINDOW_NAME = 'MY_WINDOW1';

    WINDOW_NAME
    ------------------------------
    MY_WINDOW1

Example 28-7 Creating a Window

The following statement creates a window called `my_window2` in `SYS`:

    BEGIN
    DBMS_SCHEDULER.CREATE_WINDOW (
       window_name       => 'my_window2',
       schedule_name     => 'my_stats_schedule',
       resource_plan     => 'my_resourceplan1',
       duration          => interval '60' minute,
       comments          => 'My window');
    END;
    /

See Also: PL/SQL Packages and Types Reference for detailed information about the `CREATE_WINDOW` procedure and "Creating Windows" for further information

### Example of Creating Window Groups

This section contains an example of creating a window group. To create a window group, you use the `CREATE_WINDOW_GROUP` procedure.

Example 28-8 Creating a Window Group

The following statement creates a window group called `my_window_group1`:

    BEGIN
    DBMS_SCHEDULER.CREATE_WINDOW_GROUP ('my_windowgroup1');
    END;
    /

Then, you could add three windows (`my_window1`, `my_window2`, and `my_window3`) to `my_window_group1` by issuing the following statements:

    BEGIN
    DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
       group_name   =>  'my_window_group1',
       window_list  =>  'my_window1, my_window2');

    DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
       group_name   =>  'my_window_group1',
       window_list  =>  'my_window3');
    END;
    /

To verify that the window group was created and the windows added to it, issue the following statement:

    SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS;

    WINDOW_GROUP_NAME    ENABLED   NUMBER_OF_WINDOWS   COMMENTS
    -----------------    -------   -----------------   ---------------
    MY_WINDOW_GROUP1     TRUE                      3   This is my first window group

See Also: PL/SQL Packages and Types Reference for detailed information about the `CREATE_WINDOW_GROUP` and `ADD_WINDOW_GROUP_MEMBER` procedures and "Creating Window Groups" for further information

### Examples of Setting Attributes

This section contains several examples of setting attributes. To set attributes, you use `SET_ATTRIBUTE` and `SET_SCHEDULER_ATTRIBUTE` procedures.

Example 28-9 Setting the Repeat Interval Attribute

The following example resets the frequency `my_emp_job1` will run to daily:

    BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE (
       name           =>   'my_emp_job1',
       attribute      =>   'repeat_interval',
       value          =>   'FREQ=DAILY');
    END;
    /

To verify the change, issue the following statement:

    SELECT JOB_NAME, REPEAT_INTERVAL FROM DBA_SCHEDULER_JOBS
    WHERE JOB_NAME =  'MY_EMP_JOB1';

    JOB_NAME             REPEAT_INTERVAL
    ----------------     ---------------
    MY_EMP_JOB1          FREQ=DAILY

Example 28-10 Setting the Comments Attribute

The following example resets the comments for `my_saved_program1`:

    BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE (
       name           =>   'my_saved_program1',
       attribute      =>   'comments',
       value          =>   'For nightly table stats');
    END;
    /

To verify the change, issue the following statement:

    SELECT PROGRAM_NAME, COMMENTS FROM DBA_SCHEDULER_PROGRAMS;

    PROGRAM_NAME        COMMENTS
    ------------        -----------------------
    MY_PROGRAM1         My comments here
    MY_SAVED_PROGRAM1   For nightly table stats

Example 28-11 Setting the Duration Attribute

The following example resets the duration of `my_window3` to 90 minutes:

    BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE (
       name           =>   'my_window3',
       attribute      =>   'duration',
       value          =>   interval '90' minute);
    END;
    /

To verify the change, issue the following statement:

    SELECT WINDOW_NAME, DURATION FROM DBA_SCHEDULER_WINDOWS
    WHERE WINDOW_NAME = 'MY_WINDOW3';

    WINDOW_NAME        DURATION
    -----------        ---------------
    MY_WINDOW3         +000 00:90:00

Example 28-12 Setting the Event Expiration Attribute

The following example sets the time in seconds to 3600 when an event expires:

    BEGIN
    DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE (
       attribute     =>   event_expiry_time,
       value         =>   3600);
    END;
    /

See Also: PL/SQL Packages and Types Reference for detailed information about the `SET_SCHEDULER_ATTRIBUTE` procedure and "Task 2E: Setting Scheduler Attributes"

### Examples of Creating Chains

This section contains examples of creating chains. To create chains, you use the `CREATE_CHAIN` procedure. After creating a chain, you add steps to the chain with the `DEFINE_CHAIN_STEP` procedure and define the rules with the `DEFINE_CHAIN_RULE` procedure.

Example 28-13 Creating a Chain

The following example creates a chain where `my_program1` runs before `my_program2` and `my_program3`. `my_program2` and `my_program3` run in parallel after `my_program1` has completed.

    BEGIN
    DBMS_SCHEDULER.CREATE_CHAIN (
       chain_name            =>  'my_chain1',
       rule_set_name         =>  NULL,
       evaluation_interval   =>  NULL,
       comments              =>  NULL);
    END;
    /

    --- define three steps for this chain.
    BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'step1', 'my_program1');
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'step2', 'my_program2');
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'step3', 'my_program3');
    END;
    /

    --- define corresponding rules for the chain.
    BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain1', 'TRUE', 'START step1');
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
       'my_chain1', 'step1 COMPLETED', 'Start step2, step3');
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
       'my_chain1', 'step2 COMPLETED AND step3 COMPLETED', 'END');
    END;
    /

Example 28-14 Creating a Chain

The following example creates a chain where first `my_program1` runs. If it succeeds, `my_program2` runs; otherwise, `my_program3` runs.

    BEGIN
    DBMS_SCHEDULER.CREATE_CHAIN (
       chain_name            => 'my_chain2',
       rule_set_name         => NULL,
       evaluation_interval   => NULL,
       comments              => NULL);
    END;
    /

    --- define three steps for this chain.
    BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain2', 'step1', 'my_program1');
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain2', 'step2', 'my_program2');
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain2', 'step3', 'my_program3');
    END;
    /

    --- define corresponding rules for the chain.
    BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('my_chain2', 'TRUE', 'START step1');
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
       'my_chain2', 'step1 SUCCEEDED', 'Start step2');
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
       'my_chain2', 'step1 COMPLETED AND step1 NOT SUCCEEDED', 'Start step3');
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
       'my_chain2', 'step2 COMPLETED OR step3 COMPLETED', 'END');
    END;
    /

See Also: PL/SQL Packages and Types Reference for detailed information about the `CREATE_CHAIN`, `DEFINE_CHAIN_STEP`, and `DEFINE_CHAIN_RULE` procedures and "Task 2E: Setting Scheduler Attributes"

### Examples of Creating Jobs and Schedules Based on Events

This section contains examples of creating event-based jobs and event schedules. To create event-based jobs, you use the `CREATE_JOB` procedure. To create event-based schedules, you use the `CREATE_EVENT_SCHEDULE` procedure.

These examples assume the existence of an application that, when it detects the arrival of a file on a system, enqueues an event onto the queue `my_events_q`.

Example 28-15 Creating an Event-Based Schedule

The following example illustrates creating a schedule that can be used to start a job whenever the Scheduler receives an event indicating that a file arrived on the system before 9AM:

    BEGIN
    DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
       schedule_name     =>  'scott.file_arrival',
       start_date        =>  systimestamp,
       event_condition   =>  'tab.user_data.object_owner = ''SCOTT''
          and tab.user_data.event_name = ''FILE_ARRIVAL''
          and extract hour from tab.user_data.event_timestamp < 9',
       queue_spec        =>  'my_events_q');
    END;
    /

Example 28-16 Creating an Event-Based Job

The following example creates a job that starts when the Scheduler receives an event indicating that a file arrived on the system:

    BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
       job_name            =>  my_job,
       program_name        =>  my_program,
       start_date          =>  '15-JUL-04 1.00.00AM US/Pacific',
       event_condition     =>  'tab.user_data.event_name = ''FILE_ARRIVAL''',
       queue_spec          =>  'my_events_q'
       enabled             =>  TRUE,
       comments            =>  'my event-based job');
    END;
    /

See Also: PL/SQL Packages and Types Reference for detailed information about the `CREATE_JOB` and `CREATE_EVENT_SCHEDULE` procedures